JSONL ファイルをもとに Amazon Athena で CREATE TABLE してみる

#Amazon Athena

よしなな

2024.03.29

こんにちは！よしななです。今回は、JSONL データを Amazon Athena でCREATE TABLEする機会があったので備忘録としてブログに残します。

やりたいこと

Amazon S3 にアップロードされた以下の JSONL ファイルをもとに、Amazon Athena 上でCREATE TABLEを実行します。
作成したテーブルに対し、SELECT文を使用してGradesカラムをJapanese（国語）、Math（数学）、English（英語）…と分けて出力するところまでを実施します。
今回対象となるデータは以下となります。

{"No": 1, "Name": "山田太郎", "Gender": "Male", "Grades": {"Japanese": 82, "Math": 74, "English": 88, "Science": 90, "SocialStudies": 85}}
{"No": 2, "Name": "佐藤花子", "Gender": "Female", "Grades": {"Japanese": 90, "Math": 68, "English": 95, "Science": 87, "SocialStudies": 92}}
{"No": 3, "Name": "鈴木一郎", "Gender": "Male", "Grades": {"Japanese": 75, "Math": 80, "English": 78, "Science": 85, "SocialStudies": 80}}
{"No": 4, "Name": "高橋愛", "Gender": "Female", "Grades": {"Japanese": 88, "Math": 92, "English": 94, "Science": 90, "SocialStudies": 91}}

今回対象となるデータの型は以下の通りです。

カラム名	データ型
No	int
Name	string
Gender	string
Grades	struct

対象読者

JSONL ファイルをもとに Amazon Athena 上でCREATE TABLEしたい方

事前準備

AWS マネジメントコンソールへのサインイン

AWS マネジメントコンソールからサインインが必要です。
以下の公式ドキュメントを参考に、サインインを行います。
https://docs.aws.amazon.com/ja_jp/signin/latest/userguide/how-to-sign-in.html

データを Amazon S3 に配置する

まず、JSONL ファイルをアップロードするための S3 バケットを作成します。ホーム画面上部 → 検索コンソールから S3 を検索 → バケットを作成をクリックします。
バケット作成画面に遷移するので、ここから S3 バケットを作成します。

設定項目

AWS リージョン：アジアパシフィック (東京) ap-northeast-1 を選択します。
バケット名：任意の名前を入力します。今回はjsonl-testに設定します。
その他の設定はデフォルトの状態にします。

JSONL ファイルのアップロード

作成した S3 バケットに、テーブルのデータソースとなる JSONL ファイルのアップロードを行います。作成したバケットを選択 → アップロードをクリックします。

以下の画面に遷移します。
ファイルのアップロードをクリックするとエクスプローラーが開くので、該当のファイルを選択してアップロードします。こちらでファイルのアップロードが完了となります。

CREATE DATABASE の実行

次に、Amazon Athena 上でCREATE DATABASEを実行します。
ホーム画面上部 → 検索コンソールから Amazon Athena を検索 → クエリエディタをクリックします。
クエリエディタを開いたら、以下を実行し DATABASE を作成します。

CREATE DATABASE jsonl_test;

手順

CREATE TABLE の実行

データベースが作成されたので、CREATE TABLEを実行してみます。

CREATE EXTERNAL TABLE IF NOT EXISTS `jsonl_test_table`(
  `No` int,
  `Name` string,
  `Gender` string,
  `Grades` struct <`Japanese`:int,`Math`:int,`English`:int,`Science`:int,`SocialStudies`:int>
)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
LOCATION
  's3://jsonl-test/';

CREATE TABLE クエリの構文について

上記のクエリの構文について、以下で解説します。
下記以外にもCREATE TABLEのオプションを指定できますが、JSONL ファイルの読み込みには下記の3項目が設定されていればクエリの実行が可能です。
クエリの作成には、以下の公式ドキュメントページを参考にしています。
https://docs.aws.amazon.com/athena/latest/ug/create-table.html

1.CREATE TABLE

EXTERNAL
- Amazon Athena 上でCREATE TABLEする場合、Iceberg テーブルを作成する場合以外でEXTERNALが抜けているとエラーになります。
IF NOT EXISTS
- 同じ名前のテーブルが既にデータベースに存在する場合、CREATE TABLEをスキップします。

2. ROW FORMAT SERDE

作成するテーブルのデータ形式を指定します。
テーブル作成元のデータ形式とROW FORMAT SERDEの形式が一致していないとエラーになります。
今回は、JSONL ファイルを読み込む必要があるので'org.openx.data.jsonserde.JsonSerDe'を指定します。

3. LOCATION

データが配置されているバケット名を入力します。
今回は、jsonl-test直下にtest_jsonl.jsonlを配置したのでs3://jsonl-test/を入力します。

オブジェクト型について

オブジェクト型のデータを CREATE TABLEするには、struct＜col_name:data_type,...＞と記述します。

出力結果

テーブルが作成されたので、SELECT * FROM "jsonl_test"."jsonl_test_table" limit 10;を実行しテーブルの中身を確認してみます。

問題なくテーブルが出力されました！

テーブルの検索

次に、SELECT文を使用してGradesカラムをJapanese（国語）、Math（数学）、English（英語）…と分けて出力してみたいと思います。

SELECT
    "No",
    "Name",
    "Gender",
    "Grades"."Japanese" as "国語",
    "Grades"."Math" as "数学",
    "Grades"."English" as "英語"
FROM "jsonl_test"."jsonl_test_table" limit 10;